
package com.newedu.equipmgr.dao;

import com.newedu.equipmgr.entity.SysUser;
import com.newedu.equipmgr.util.MyDBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.List;


public class SysUserDaoImpl implements SysUserDao {
	// 创建一个执行器对象,提供连接池对象作为构造方法的参数
	private QueryRunner queryRunner = new QueryRunner(MyDBUtils.getDataSource());
	/**
	 * 用户登陆，根据用户名查询数据库，并且返回数据对象SysUser
	 *
	 * @param username 用户名参数
	 * @return 数据库中存在的数据对象SysUser
	 * @throws SQLException
	 */
	@Override
	public SysUser login(String username) throws SQLException {
		// 对参数进行合法性校验
		if(username==null||username.isEmpty()){
			throw new IllegalArgumentException("参数不正确 : " + username);
		}
		String sql = "select * from t_users where username = ?";
		SysUser sysUser = queryRunner.query(sql, new BeanHandler<SysUser>(SysUser.class), username);
		return sysUser;
	}

	/**
	 * 插入 用户数据，并且返回是否插入成功
	 *
	 * @param sysUser 数据对象SysUser
	 * @return 是否插入成功，true-成功，false-失败
	 * @throws SQLException
	 */
	@Override
	public boolean insert(SysUser sysUser) throws SQLException {
		// 对参数进行合法性校验
		if(sysUser == null){
			throw new IllegalArgumentException("参数不正确 : " + sysUser);
		}
		String sql = "insert into `equipmentmgr`.`t_users`\n" +
				"            (`id`,\n" +
				"             `username`,\n" +
				"             `pwd`)\n" +
				"values (null,\n" +
				"        ?,\n" +
				"        ?);";
		Object[] params = {
				sysUser.getUsername(),
				sysUser.getPwd()};
		int insert = queryRunner.update(sql, params);
		return insert == 1?true:false;
	}

	/**
	 * 更新操作，并返回是否更新成功
	 *
	 * @param sysUser 数据对象SysUser
	 * @return 是否更新成功，true-成功，false-失败
	 * @throws SQLException
	 */
	@Override
	public boolean update(SysUser sysUser, String roleId) throws SQLException {
		// 对参数进行合法性校验
		if(sysUser == null){
			throw new IllegalArgumentException("参数不正确 : " + sysUser);
		}

		String sql1 = "update t_users set username = ?, pwd = ? where id = ?";
		Object[] params1 = {
				sysUser.getUsername(),
				sysUser.getPwd(),
				sysUser.getId()};
		int update1 = queryRunner.update(sql1, params1);

		String sql2 = "update t_users_roles set roleid=? where userid=?";
		Object[] params2 = {
				Integer.parseInt(roleId),
				sysUser.getId()};
		int update2 = queryRunner.update(sql2, params2);
		int update=update1+update2;
		return update == 2?true:false;
	}

	/**
	 * 删除操作，并返回是否删除成功
	 *
	 * @param id 用户数据的主键id
	 * @return 是否删除成功，true-成功，false-失败
	 * @throws SQLException
	 */
	@Override
	public boolean delete(int id) throws SQLException {
		// 对参数进行合法性校验
		if(id == 0){
			throw new IllegalArgumentException("参数不正确 : " + id);
		}

		String sql = "delete from t_users where id = ?";
		int delete = queryRunner.update(sql, id);
		return delete == 1?true:false;
	}

	/**
	 * 查询数据库表中的数据，返回所有的结果集对象
	 *
	 * @return 结果集对象 List<SysUser>
	 * @throws SQLException
	 */
	@Override
	public List<SysUser> selectAll() throws SQLException {
		String sql = "select * from t_users";
		List<SysUser> userInfoList = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class));
		return userInfoList;
	}

	/**
	 * 根据用户主键id，查询一笔数据，并返回数据对象SysUser
	 *
	 * @param id 用户的主键id
	 * @return 数据对象SysUser
	 * @throws SQLException
	 */
	@Override
	public SysUser selectOne(int id) throws SQLException {
		// 对参数进行合法性校验
		if(id == 0){
			throw new IllegalArgumentException("参数不正确 : " + 0);
		}
		String sql = "select * from t_users where id = ?";
		SysUser userInfo = queryRunner.query(sql, new BeanHandler<SysUser>(SysUser.class), id);
		return userInfo;
	}

	/**
	 * 根据用户输入的查询条件 ,例如，用户名等等，和分页情况下页面值，返回查询到结果集对象
	 *
	 * @param condition 查询条件
	 * @param pageNum   分页的页码
	 * @return 查询到的结果集对象 List<SysUser>
	 * @throws SQLException
	 */
	@Override
	public List<SysUser> selectBy(String condition, int pageNum, int pageSize) throws SQLException {
		String sql = "select * from t_users ";
		// 对参数进行合法性校验
		if(condition != null && !condition.isEmpty()){
			sql += condition;
		}
		if(pageNum != 0){
			sql += " limit "+ pageSize*(pageNum-1) +" , "+ pageSize ;
		}

		System.out.println(sql);
		List<SysUser> userInfoList = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class));
		return userInfoList;
	}

	/**
	 * 获取表中所有的行数，用于分页
	 *
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int countRows(String condition) throws SQLException {
		String sql = "select count(*) from t_users ";
		// 对参数进行合法性校验
		if(condition != null && !condition.isEmpty()){
			sql += condition;
		}
		Object query = queryRunner.query(sql, new ScalarHandler<>());
		return Integer.parseInt(query.toString());
	}

	@Override
	public boolean insertUserAndRoleRelationship(SysUser sysUser, int roleId) throws SQLException{
		// 对参数进行合法性校验
		if(sysUser == null){
			throw new IllegalArgumentException("参数不正确 : " + sysUser);
		}
		// 因为要执行多次sql语句，需要用到同一个Connection对象
		Connection connection = MyDBUtils.getConnection();

		int rows = 0;
		try{
			//取消自动提交事务
			connection.setAutoCommit(false);

			String sql = "insert into `equipmentmgr`.`t_users`\n" +
					"            (`id`,\n" +
					"             `username`,\n" +
					"             `pwd`)\n" +
					"values (null,\n" +
					"        ?,\n" +
					"        ?);";

			String sql2 = "insert into `equipmentmgr`.`t_users_roles`\n" +
					"            (`id`,\n" +
					"             `userid`,\n" +
					"             `roleid`)\n" +
					"values (null,\n" +
					"        ?,\n" +
					"        ?);";

			PreparedStatement pstm = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstm.setString(1,sysUser.getUsername());
			pstm.setString(2,sysUser.getPwd());

			pstm.executeUpdate();
			ResultSet generatedKeys = pstm.getGeneratedKeys();
			int userId = generatedKeys.next()?generatedKeys.getInt(1):-1;


			// 执行用户与角色关系表的插入操作
			pstm = connection.prepareStatement(sql2);
			pstm.setInt(1,userId);
			pstm.setInt(2,roleId);
			rows = pstm.executeUpdate();

			connection.commit();
		}catch (SQLException e){
			connection.rollback();
			throw e;
		}
		finally {
			connection.close();
		}

		return rows == 1?true:false;
	}

	@Override
	public SysUser login(String username, String pwd) throws SQLException {
		// 对参数进行合法性校验
		if(username == null || username.isEmpty() ){
			throw new IllegalArgumentException("参数不正确 : " + username);
		}
		if(pwd == null || pwd.isEmpty()){
			throw new IllegalArgumentException("参数不正确 : " + pwd);
		}
		String sql = "select * from t_users where username = ? and pwd=?";
		SysUser userInfo = queryRunner.query(sql, new BeanHandler<>(SysUser.class), new Object[]{username,pwd});
		return userInfo;
	}

}
